package edu.fudan.cse.medlab.event.filter;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.LinkedList;

import edu.fudan.cse.medlab.event.extraction.DB;


public class DataBase {
	private static Connection connection;
	private static int recordCount;

//	 private static void open() {
//	    try {
//	    	Settings setting = new Settings();
//	    	Class.forName("com.mysql.jdbc.Driver").newInstance();
//			connection = java.sql.DriverManager.getConnection(
//					"jdbc:mysql://"+setting.host+":3306/"+setting.dbname
//					+"?useUnicode=true&characterEncoding=utf8",setting.username,setting.password);	
//	       }
//	    catch (Exception e) {
//	        e.printStackTrace();
//	    }
//	 }
	
//	public static void open(String host,String dbname,String username,String password){
//		host = host.trim();
//		dbname = dbname.trim();
//		username = username.trim();
//		password = password.trim();
//		try{
//			Class.forName("com.mysql.jdbc.Driver").newInstance();
//			connection = java.sql.DriverManager.getConnection("jdbc:mysql://"+host+":3306/"+dbname+"?useUnicode=true&characterEncoding=utf8",username,password);
//			
//		}
//		catch(Exception e){
//			e.printStackTrace();
//		}
//	}
//	
//	public static void close()
//	{
//		try{
//			if(connection!=null)
//			{
//				connection.close();
//			}
//		}
//		catch(SQLException e){
//			e.printStackTrace();
//		}
//	}
	
	
//	public static boolean writeLog(Timestamp time, String info) throws Exception{
//		connection = DB.getConnection();
//		PreparedStatement preparedStatement = connection.prepareStatement("insert into logger values(?,?,id)");
//		preparedStatement.setTimestamp(1, time);
//		preparedStatement.setString(2,info);
//		boolean ok =(preparedStatement.executeUpdate()==1);
//		preparedStatement.close();
//		connection.close();
//		if (ok)	
//			return true;			
//		else
//			return false;
//	}
	
//	public static ResultSet readLog() throws SQLException{
//		connection = DB.getConnection();
//		PreparedStatement preparedStatement = connection.prepareStatement("select log from logger");
//		ResultSet resultSet=preparedStatement.executeQuery();
//		preparedStatement.close();
//		connection.close();
//		return resultSet;
//	}
//	
//	public static ResultSet readLogAfterTime(Timestamp time) throws SQLException{
//		connection = DB.getConnection();
//		PreparedStatement preparedStatement = connection.prepareStatement("select log from logger where timestamp >"+time);
//		ResultSet resultSet=preparedStatement.executeQuery();
//		preparedStatement.close();
//		connection.close();
//		return resultSet;
//	}
	
//	public static boolean insertToLocation(String location, String address, String des)throws SQLException{
//		
//		connection = DB.getConnection();
//		PreparedStatement preparedStatement = connection.prepareStatement("insert into location values(LID,?,?,?,0,0)");
//		preparedStatement.setString(1,location);
//		preparedStatement.setString(2,address);
//		preparedStatement.setString(3,des);
//		boolean ok =(preparedStatement.executeUpdate()==1);
//		preparedStatement.close();
//		connection.close();
//		if (ok)	
//			return true;			
//		else
//			return false;
//	}
	
//	public static boolean insertToAlias(String location,String alias) throws SQLException{
//		connection = DB.getConnection();
//		if(alias.equals(""))
//			return false;
//		PreparedStatement preparedStatement = connection.prepareStatement("select * from location where Name =?");
//		preparedStatement.setString(1,location);
//		ResultSet resultSet=preparedStatement.executeQuery();
//		resultSet.first();
//		int LID = resultSet.getInt("LID");
//		
//		preparedStatement = connection.prepareStatement("insert into alias values(AID,?,?)");
//		preparedStatement.setInt(1,LID);
//		preparedStatement.setString(2,alias);
//		resultSet.close();
//		boolean ok =(preparedStatement.executeUpdate()==1);
//		preparedStatement.close();
//		connection.close();
//		if (ok)	
//			return true;			
//		else
//			return false;
//	}
	

	public static String[] getAllLocation() {
		String all = "";
		try{
			connection = DB.getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("select * from Location");
			ResultSet resultSet=preparedStatement.executeQuery();
			String lname;
			
			while (resultSet.next()) {
				lname= resultSet.getString("Name");
				all += lname+"?";
			}
			PreparedStatement preparedStatement2 = connection.prepareStatement("select * from Alias");
			ResultSet resultSet2=preparedStatement2.executeQuery();
			while (resultSet2.next()) {
				lname= resultSet2.getString("Name");
				all += lname+"?";
			}
			all = all.substring(0,all.length()-1);
			resultSet2.close();
			resultSet.close();
			preparedStatement.close();
			preparedStatement2.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		return all.split("\\?");
	}

	
//	public static String getLocationByLID(int lid) throws SQLException{
//		connection = DB.getConnection();
//		PreparedStatement preparedStatement = connection.prepareStatement("select * from Location where LID=?");
//		preparedStatement.setInt(1,lid);
//		ResultSet resultSet=preparedStatement.executeQuery();
//		resultSet.next();
//		String s = resultSet.getString("Name");
//		resultSet.close();
//		preparedStatement.close();
//		connection.close();
//		return s;
//	}
	
	public static String[] getAllInfo() {
		String[] infostr=null;
		try{
			connection = DB.getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("select * from event");
			ResultSet rs=preparedStatement.executeQuery();
			Timestamp time;
			String location,d,title;
			LinkedList<String> info = new LinkedList();
			while (rs.next()) {
				time = rs.getTimestamp("startTime");
				d = new String("\""+String.valueOf(time.getYear()+1900)+"?"+String.valueOf(time.getMonth()+1)
						+"?"+String.valueOf(time.getDate())+"\"");
				location = "\""+rs.getString("location")+"\"";
				title ="\""+rs.getString("eventName")+"\"";
				info.add(d);
				info.add(location);
				info.add(title);
			}
			infostr = info.toArray(new String[0]);
			rs.close();
			preparedStatement.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{	
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		return infostr;
	}
	
	
	public static String[] getTuple() {
		String[] infostr=null;
		try{
			connection = DB.getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("select * from event");
			ResultSet rs=preparedStatement.executeQuery();
			Timestamp time;
			String location,d,title;
			LinkedList<String> info = new LinkedList();
			while (rs.next()) {
				time = rs.getTimestamp("startTime");
				d = time.toString().substring(0, time.toString().indexOf("."));
				location = rs.getString("location");
				title =rs.getString("eventName");
				info.add(title);
				info.add(d);
				info.add(location);	
			}
			infostr = info.toArray(new String[0]);
			rs.close();
			preparedStatement.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{	
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		return infostr;
	}
	
	public static String[] getAllTitle(){
		LinkedList<String> titles = new LinkedList();
		try{
			connection = DB.getConnection();
			String temp;
			PreparedStatement preparedStatement = connection.prepareStatement("select eventName from event");
			ResultSet resultSet=preparedStatement.executeQuery();	
			while (resultSet.next()) {
				temp= resultSet.getString("eventName");
				titles.add(temp);
			}
			resultSet.close();
			preparedStatement.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{	
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		return titles.toArray(new String[0]);	
	}
	
	public static String[] getAllDistinctTitle(int k){
		LinkedList<String> titles = new LinkedList();
		try{
			connection = DB.getConnection();
			String temp;
			PreparedStatement preparedStatement = connection.prepareStatement("select distinct eventName from event");
			ResultSet resultSet=preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				temp= resultSet.getString("eventName");
				titles.add(temp);
			}
			resultSet.close();
			preparedStatement.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{	
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		return titles.toArray(new String[0]);
		
	}
	
	public static String[] getEventTimeByLoc(String location){
		LinkedList<String> info = new LinkedList();
		try{
			connection = DB.getConnection();
			String temp;
			Timestamp time;
			PreparedStatement preparedStatement = connection.prepareStatement("select eventName,startTime from event where normal_location=? order by startTime");
			preparedStatement.setString(1,location);
			ResultSet resultSet=preparedStatement.executeQuery();
			while (resultSet.next()) {
				temp = resultSet.getString("eventName");
				info.add(temp);
				time = resultSet.getTimestamp("startTime");
				temp = time.toString().substring(0, time.toString().indexOf("."));
				info.add(temp);
			}
			resultSet.close();
			preparedStatement.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{	
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		return info.toArray(new String[0]);
	}
	
	public static boolean deleteEvent(){
		boolean ok = false;
		try{
			connection = DB.getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("delete from event");
			ok =(preparedStatement.executeUpdate()==1);
			preparedStatement.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		if (ok)	
			return true;			
		else
			return false;
	
	}
	
//	public static ResultSet getAllDistinctTitle() throws SQLException{
//		connection = DB.getConnection();
//		PreparedStatement preparedStatement = connection.prepareStatement("select distinct title from Event");
//		ResultSet resultSet=preparedStatement.executeQuery();
//		preparedStatement.close();
//		connection.close();
//		return resultSet;
//	}
	
//	public static int getEventCount(int lid) throws SQLException{
//		connection = DB.getConnection();
//		PreparedStatement preparedStatement = connection.prepareStatement("select * from Location where LID=?");
//		preparedStatement.setInt(1,lid);
//		ResultSet resultSet=preparedStatement.executeQuery();
//		if(DataBase.getRecordCount(resultSet) == 0)
//			return -1;
//		
//		PreparedStatement preparedStatement2 = connection.prepareStatement("select  title from Event where LID=?");
//		preparedStatement2.setInt(1, lid);
//		ResultSet resultSet2=preparedStatement2.executeQuery();
//		int k = DataBase.getRecordCount(resultSet2);
//		resultSet2.close();
//		resultSet.close();
//		preparedStatement.close();
//		preparedStatement2.close();
//		connection.close();
//		return k;
//	}
	
//	public static int getDistinctEventCount(int lid) throws SQLException{
//		connection = DB.getConnection();
//		PreparedStatement preparedStatement = connection.prepareStatement("select * from Location where LID=?");
//		preparedStatement.setInt(1,lid);
//		ResultSet resultSet=preparedStatement.executeQuery();
//		if(DataBase.getRecordCount(resultSet) == 0)
//			return -1;
//		
//		PreparedStatement preparedStatement2 = connection.prepareStatement("select distinct title from Event where LID=?");
//		preparedStatement2.setInt(1, lid);
//		ResultSet resultSet2=preparedStatement2.executeQuery();
//	
//		int k = DataBase.getRecordCount(resultSet2);
//		resultSet2.close();
//		resultSet.close();
//		preparedStatement.close();
//		preparedStatement2.close();
//		connection.close();
//		return k;
//	}
	
	public static boolean addURL(String url){
		boolean ok = false;
		try{
			connection = DB.getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("select * from url where url=?");
			preparedStatement.setString(1,url);
			ResultSet resultSet=preparedStatement.executeQuery();
			if(DataBase.getRecordCount(resultSet)==1)
				return false;
			//(40,'sh.nihaopiao.com',173,80,4,196658,NULL,10,NULL,1,'0',2,255,0,0,0,0),
			PreparedStatement preparedStatement2 = connection.prepareStatement("insert into url values(urlid,?,0,0,0,0,NULL,0,0,0,0,0,0,0,0,0,0)");
			preparedStatement2.setString(1,url);
			ok =(preparedStatement2.executeUpdate()==1);
			preparedStatement.close();
			preparedStatement2.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		if (ok)	
			return true;			
		else
			return false;
	}
	
	public static boolean updateURLref1(String s,int k){
		boolean ok = false;
		try{
			connection = DB.getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("update url set ref1=? where url=?");
			preparedStatement.setInt(1,k);
			preparedStatement.setString(2,s);
			ok =(preparedStatement.executeUpdate()==1);
			preparedStatement.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		if (ok)	
			return true;			
		else
			return false;
	}
	
	public static boolean updateURLref2(String s,int k){
		boolean ok = false;
		try{
			connection = DB.getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("update url set ref2=? where url=?");
			preparedStatement.setInt(1,k);
			preparedStatement.setString(2,s);
			ok =(preparedStatement.executeUpdate()==1);
			preparedStatement.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		if (ok)	
			return true;			
		else
			return false;
	}
	
	public static boolean updateURLref3(String s,int k) {
		boolean ok = false;
		try{
			connection = DB.getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("update url set ref3=? where url=?");
			preparedStatement.setInt(1,k);
			preparedStatement.setString(2,s);
			ok =(preparedStatement.executeUpdate()==1);
			preparedStatement.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		if (ok)	
			return true;			
		else
			return false;
	}
	
	public static boolean updateURLref(){
		boolean ok = false;
		try{
			connection = DB.getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("update url set ref= (ref1+ref2+ref3)");
			ok =(preparedStatement.executeUpdate()==1);
			preparedStatement.close();
			
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		if (ok)	
			return true;			
		else
			return false;
	}
	
	
	public static boolean updateURLpagerank(String s,int k){
		boolean ok = false;
		try{
			connection = DB.getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("update url set pagerank=? where url=?");
			preparedStatement.setInt(1,k);
			preparedStatement.setString(2,s);
			ok =(preparedStatement.executeUpdate()==1);
			preparedStatement.close();		
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		if (ok)	
			return true;			
		else
			return false;
	}
	
	public static boolean updateURLalexa(String s,int k){
		boolean ok = false;
		try{
			connection = DB.getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("update url set alexa=? where url=?");
			preparedStatement.setInt(1,k);
			preparedStatement.setString(2,s);
			ok =(preparedStatement.executeUpdate()==1);
			preparedStatement.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		if (ok)	
			return true;			
		else
			return false;
	}
	
	public static boolean updateByLogFile(String s,String  size, int pagecount, int minutes){
		boolean ok = false;
		try{
			connection = DB.getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("update url set size=?,pagecount=?,minutes=? where url=?");
			preparedStatement.setString(1,size);
			preparedStatement.setInt(2,pagecount);
			preparedStatement.setInt(3, minutes);
			preparedStatement.setString(4, s);
			ok =(preparedStatement.executeUpdate()==1);
			preparedStatement.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		if (ok)	
			return true;			
		else
			return false;
	}
	
	public static boolean updateURLFilterPage(String s,int filterpage){
		boolean ok = false;
		try{
			connection = DB.getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("update url set targetcount=? where url=?");
			preparedStatement.setInt(1,filterpage);
			preparedStatement.setString(2, s);
			ok =(preparedStatement.executeUpdate()==1);
			preparedStatement.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		if (ok)	
			return true;			
		else
			return false;
	}
	
	public static boolean updateURLPageCount(String s,int pagecount) {
		boolean ok = false;
		try{
			connection = DB.getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("update url set pagecount=? where url=?");
			System.out.println(s+"   "+pagecount);
			preparedStatement.setInt(1,pagecount);
			preparedStatement.setString(2, s);
			ok =(preparedStatement.executeUpdate()==1);
			preparedStatement.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		if (ok)	
			return true;			
		else
			return false;
		}
	
//	public static ResultSet getURL() {
//		ResultSet resultSet=null;
//		try{
//			connection = DB.getConnection();
//			PreparedStatement preparedStatement = connection.prepareStatement("select url,pagerank,alexa from url");
//			resultSet=preparedStatement.executeQuery();
//			preparedStatement.close();
//		}
//		catch(Exception e){
//			e.printStackTrace();
//		}
//		finally{
//			try{
//				connection.close();
//			}
//			catch(Exception e){};
//		}
//		return resultSet;
//	}
	
//	public static ResultSet getURLreflt(int k){
//		ResultSet resultSet=null;
//		try{
//			connection = DB.getConnection();
//			PreparedStatement preparedStatement = connection.prepareStatement("select url from url where ref1>="+k+" order by -ref1");
//			resultSet=preparedStatement.executeQuery();
//			preparedStatement.close();
//		}
//		catch(Exception e){
//			e.printStackTrace();
//		}
//		finally{
//			try{
//				connection.close();
//			}
//			catch(Exception e){};
//		}
//		return resultSet;
//	}
	
	public static String[] getURLByRef(int sitecount,String block){
		
		LinkedList<String> urls = new LinkedList();
		try{
			connection = DB.getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("select url from url  order by -ref");
			ResultSet rs=preparedStatement.executeQuery();
			int count = 0;
			while(rs.next()){
				if(count>=sitecount)
					break;
				String s = rs.getString("url");
				if(block.contains(s))
					continue;
				else
					urls.add(s);
			}
			preparedStatement.close();
			rs.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		return urls.toArray(new String[0]);
	}
	
	public static boolean setState(int k){
		boolean ok = false;
		try{
			connection = DB.getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("update state set state=?");
			preparedStatement.setInt(1, k);	
			ok =(preparedStatement.executeUpdate()==1);
			preparedStatement.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		if (ok)	
			return true;			
		else
			return false;
	}
	
	public static String[] getSitesByRef(String ref, int count) {
		String[] sites = null;
		try{
			connection = DB.getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("select url from url order by -"+ref);
			ResultSet resultSet=preparedStatement.executeQuery();
			sites = new String[count];
			for (int i = 0; i < count; i++) {
				resultSet.next();
				sites[i] = resultSet.getString("url");
			}
			resultSet.close();
			preparedStatement.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		return sites;
	}
	
	public static int getEventNumForSite(String site){
		int k = 0;
		try{
			connection = DB.getConnection();
			site = site.replace("\\", "\\\\");
			PreparedStatement preparedStatement = connection.prepareStatement("select count(distinct event_id) from url_event where  url like BINARY '%" + site + "%'");
			ResultSet resultSet=preparedStatement.executeQuery();
			resultSet.next();
			k =resultSet.getInt("count(distinct event_id)");
			resultSet.close();
			preparedStatement.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		return k;
	}
	
	public static int getState(){
		int k = 0;
		try{
			connection = DB.getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement("select * from state");
			ResultSet resultSet=preparedStatement.executeQuery();
			resultSet.next();
			k = resultSet.getInt("state");
			resultSet.close();
			preparedStatement.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				connection.close();
			}
			catch(Exception e){};
		}
		return k;
	}
	
	
	public static int getRecordCount(ResultSet resultSet)
	  {
	    try
	    {
	      if(resultSet.last())
	      {
	        recordCount = resultSet.getRow();
	      }
	      else
	      {
	        recordCount = 0;
	      }
	      
	    }
	    catch(java.sql.SQLException e){
	    	e.printStackTrace();
	    }
	    return recordCount;
	  }
	
	public static void main(String[] args){
	
		System.out.println(DataBase.getState());
		System.out.println(DataBase.setState(1));
		System.out.println(DataBase.getState());
		
	}
}
